package cn.edu.scau.cmi.yanghongyu.dao;

import java.util.Set;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.hibernate.Session;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.edu.scau.cmi.ema.dao.AbstractJpaDao;

@Repository("FrontEchartDAO")
@Transactional
public class FrontEchartDAOImplByJDBC extends AbstractJpaDao implements FrontEchartDAO {

	@PersistenceContext(unitName = "ema")
	private EntityManager entityManager;

	@Override
	public EntityManager getEntityManager() {
		return entityManager;
	}

	@Override
	public Set getTypes() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean canBeMerged(Object o) {
		// TODO Auto-generated method stub
		return false;
	}



	@Override
	public boolean updateFoodtypeIdBySql(Integer eventId, Integer foodtypeId) {
		Session session = entityManager.unwrap(Session.class);
		String updateSql = "update echart " + "set foodtypeid = ? " + "where eventid = ?";
		int executeUpdate = session.createSQLQuery(updateSql).setInteger(0, foodtypeId).setInteger(1, eventId)
				.executeUpdate();
		return executeUpdate == 1 ? true : false;
	}



	@Override
	public boolean updateOrSave2echartBySql(Integer eventId, String eventName, String sourceCountry, String sourceProvince,
			String sourceCity, String discoveryCountry, String discoveryProvince, String discoveryCity,
			String foodtypeName) {
		return updateOrSave2echartBySql(eventId, eventName, sourceCountry, sourceProvince, sourceCity, discoveryCountry,
				discoveryProvince, discoveryCity, foodtypeName, null);
	}
	
	@Override
	public boolean updateOrSave2echartBySql(Integer eventId, String eventName, String sourceCountry, String sourceProvince,
			String sourceCity, String discoveryCountry, String discoveryProvince, String discoveryCity,
			String foodtypeName, Integer foodtypeId) {
		Session session = entityManager.unwrap(Session.class);
		// 查看eventId是否存在，存在就update, 不存在就insert
		String sql = "select exists (select 1 from echart where eventid = ? limit 1)"; // 存在返回1，不存在返回0
		int isExists = Integer.parseInt(session.createSQLQuery(sql).setInteger(0, eventId).list().get(0).toString());
		System.out.println(isExists == 0 ? "插入" : "更新");
		if (isExists == 1) {
			String updateSql = "update echart " + "set eventname = ?, sourcecountry = ?, sourceprovince = ?, "
					+ "sourcecity = ?, discoverycountry = ?, discoveryprovince = ?, discoverycity = ?, "
					+ "foodtypename = ?, foodtypeid = ? " + "where eventid = ?";
			int executeUpdate = session.createSQLQuery(updateSql).setString(0, eventName).setString(1, sourceCountry)
					.setString(2, sourceProvince).setString(3, sourceCity).setString(4, discoveryCountry)
					.setString(5, discoveryProvince).setString(6, discoveryCity).setString(7, foodtypeName)
					.setInteger(8, foodtypeId).setInteger(9, eventId).executeUpdate();
			if (executeUpdate != 1)
				return false;
		} else if (isExists == 0) {
			String insertSql = "insert into echart(eventid, eventname, sourcecountry, "
					+ "sourceprovince, sourcecity, discoverycountry, discoveryprovince, "
					+ "discoverycity, foodtypename, foodtypeid) " + "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			int executeUpdate = session.createSQLQuery(insertSql).setInteger(0, eventId).setString(1, eventName)
					.setString(2, sourceCountry).setString(3, sourceProvince).setString(4, sourceCity)
					.setString(5, discoveryCountry).setString(6, discoveryProvince).setString(7, discoveryCity)
					.setString(8, foodtypeName).setInteger(9, foodtypeId).executeUpdate();
			if (executeUpdate != 1)
				return false;
		}
		return true;
	}

	
	@Override
	public int deleteEchartsBySql(Integer id) {
		Session session = entityManager.unwrap(Session.class);
		String sql = "delete from echart where eventid = ?";
		return session.createSQLQuery(sql).setInteger(0, id).executeUpdate();
	}
}